// Get the path of the currently running do-file
local do_file_path `c(pwd)'

// Change the working directory to the location of the do-file
cd "`do_file_path'"

clear

use "proc_data\grid_ntltopcode2020-08-04.dta"

rename *, lower

keep objectid sf_area_km2-count_ntl63shntl_f182013

g ntl63sh_f101992 = sum_ntl63shntl_f101992 / count_ntl63shntl_f101992
g ntl63sh_f101993 = sum_ntl63shntl_f101993 / count_ntl63shntl_f101993
g ntl63sh_f101994 = sum_ntl63shntl_f101994 / count_ntl63shntl_f101994 
g ntl63sh_f121994 = sum_ntl63shntl_f121994 / count_ntl63shntl_f121994
g ntl63sh_f121995 = sum_ntl63shntl_f121995 / count_ntl63shntl_f121995
g ntl63sh_f121996 = sum_ntl63shntl_f121996 / count_ntl63shntl_f121996
g ntl63sh_f121997 = sum_ntl63shntl_f121997 / count_ntl63shntl_f121997
g ntl63sh_f121998 = sum_ntl63shntl_f121998 / count_ntl63shntl_f121998
g ntl63sh_f121999 = sum_ntl63shntl_f121999 / count_ntl63shntl_f121999

g ntl63sh_f141997 = sum_ntl63shntl_f141997 / count_ntl63shntl_f141997
g ntl63sh_f141998 = sum_ntl63shntl_f141998 / count_ntl63shntl_f141998
g ntl63sh_f141999 = sum_ntl63shntl_f141999 / count_ntl63shntl_f141999
g ntl63sh_f142000 = sum_ntl63shntl_f142000 / count_ntl63shntl_f142000
g ntl63sh_f142001 = sum_ntl63shntl_f142000 / count_ntl63shntl_f142001
g ntl63sh_f142002 = sum_ntl63shntl_f142000 / count_ntl63shntl_f142002
g ntl63sh_f142003 = sum_ntl63shntl_f142000 / count_ntl63shntl_f142003

g ntl63sh_f152000 = sum_ntl63shntl_f152000 / count_ntl63shntl_f152000
g ntl63sh_f152001 = sum_ntl63shntl_f152001 / count_ntl63shntl_f152001
g ntl63sh_f152002 = sum_ntl63shntl_f152002 / count_ntl63shntl_f152002
g ntl63sh_f152003 = sum_ntl63shntl_f152003 / count_ntl63shntl_f152003
g ntl63sh_f152004 = sum_ntl63shntl_f152004 / count_ntl63shntl_f152004
g ntl63sh_f152005 = sum_ntl63shntl_f152005 / count_ntl63shntl_f152005
g ntl63sh_f152006 = sum_ntl63shntl_f152006 / count_ntl63shntl_f152006
g ntl63sh_f152007 = sum_ntl63shntl_f152007 / count_ntl63shntl_f152007

g ntl63sh_f162004 = sum_ntl63shntl_f162004 / count_ntl63shntl_f162004
g ntl63sh_f162005 = sum_ntl63shntl_f162005 / count_ntl63shntl_f162005
g ntl63sh_f162006 = sum_ntl63shntl_f162006 / count_ntl63shntl_f162006
g ntl63sh_f162007 = sum_ntl63shntl_f162007 / count_ntl63shntl_f162007
g ntl63sh_f162008 = sum_ntl63shntl_f162008 / count_ntl63shntl_f162008
g ntl63sh_f162009 = sum_ntl63shntl_f162009 / count_ntl63shntl_f162009

g ntl63sh_f182010 = sum_ntl63shntl_f182010 / count_ntl63shntl_f182010
g ntl63sh_f182011 = sum_ntl63shntl_f182011 / count_ntl63shntl_f182011
g ntl63sh_f182012 = sum_ntl63shntl_f182012 / count_ntl63shntl_f182012
g ntl63sh_f182013 = sum_ntl63shntl_f182013 / count_ntl63shntl_f182013

keep objectid sf_area_km2 ntl63sh_f101992-ntl63sh_f182013

sum

** reshape to wide
reshape long ntl63sh_f10 ntl63sh_f12 ntl63sh_f14 ntl63sh_f15 ntl63sh_f16 ntl63sh_f18, i(objectid) j(year)

** overall ntl_sh
g ntl63sh = ntl63sh_f18 if year >= 2010
replace ntl63sh = ntl63sh_f16 if year < 2010 & year >= 2004
replace ntl63sh = ntl63sh_f14 if year < 2004 & year >= 1998
replace ntl63sh = ntl63sh_f12 if year < 1998 & year >= 1995
replace ntl63sh = ntl63sh_f10 if year < 1995 & year >= 1992

sum ntl63sh

sort objectid year
save "proc_data\NTLTOPCODE_ts.dta", replace